﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace WebApplication1
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        protected const string connectionString = @"Provider=SQLOLEDB;server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook;Trusted_Connection=False";
        protected const string query = "SELECT * FROM Recette where id_recette='{0}'";

        protected const string query2 = "SELECT I.nom,quantite,I.unite from Ingredient I" +
            " join Composition on I.IdIngredient=ingredient_id join Recette on id_recette=recette_id where id_recette='{0}'";
       
        bool achange = false;
        bool ajout = false;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                string id_rec = Request.QueryString["id_recette"];
                OleDbDataAdapter da = new OleDbDataAdapter(string.Format(query,id_rec),connectionString);
                da.TableMappings.Add("Table", "Recette");

                DataSet ds = new DataSet();
                da.Fill(ds);

                Session["RECETTE"] = ds;

                for (int idx = 0; idx < ds.Tables["Recette"].Rows.Count; idx++)
                {
                    TextBox1.Text = ds.Tables["Recette"].Rows[idx]["nom"].ToString();
                    TextBox2.Text = ds.Tables["Recette"].Rows[idx]["sous_titre"].ToString();
                    TextBox3.Text = ds.Tables["Recette"].Rows[idx]["nb_personnes"].ToString();
                    TextBox4.Text = ds.Tables["Recette"].Rows[idx]["tps_preparation"].ToString();
                    TextBox5.Text = ds.Tables["Recette"].Rows[idx]["tps_cuisson"].ToString();
                    TextBox6.Text = ds.Tables["Recette"].Rows[idx]["cout"].ToString();
                    TextBox7.Text = ds.Tables["Recette"].Rows[idx]["difficulte"].ToString();
                    TextBox9.Text = ds.Tables["Recette"].Rows[idx]["preparation"].ToString();
                    TextBox10.Text = ds.Tables["Recette"].Rows[idx]["realisation"].ToString();
                    TextBox8.Text = ds.Tables["Recette"].Rows[idx]["source_recette"].ToString();
                }

                //string rec_id = Request.QueryString["recette_id"];
                OleDbDataAdapter da2 = new OleDbDataAdapter(string.Format(query2,id_rec), connectionString);
                da2.TableMappings.Add("Table", "Composition");

                DataSet ds2 = new DataSet();
                da2.Fill(ds2);

                DetailIngredient.DataSource = ds2;
                DetailIngredient.DataBind();
            }
        }

        protected void DeleteButton_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            SqlConnection con;
            //Here we declare the parameter which we have to use in our application
            SqlCommand cmd = new SqlCommand();
            SqlParameter sp1 = new SqlParameter();
            
            con = new SqlConnection("server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook;Trusted_Connection=False");

            cmd = new SqlCommand("dbo.deleterecette", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            cmd.Parameters.Add("@idrec", SqlDbType.VarChar, 50).Value =Request.QueryString["id_recette"];

            try
            {
                cmd.ExecuteNonQuery();
            }                
            catch (SqlException ex)
            {
                Response.Write(ex.Message);
            }
            con.Close();
            TextBox1.Text = String.Empty;
            TextBox2.Text = String.Empty;
            TextBox3.Text = String.Empty;
            TextBox4.Text = String.Empty;
            TextBox5.Text = String.Empty;
            TextBox6.Text = String.Empty;
            TextBox7.Text = String.Empty;
            TextBox8.Text = String.Empty;
            TextBox9.Text = String.Empty;
            TextBox10.Text = String.Empty;
            DetailIngredient.Visible=false;
        }

        protected void UpdateButton_Click(object sender, EventArgs e)
        {
            if (achange)
            {
                DataSet ds = new DataSet();
                SqlConnection con;
                //Here we declare the parameter which we have to use in our application
                SqlCommand cmd = new SqlCommand();
                SqlParameter sp1 = new SqlParameter();
                SqlParameter sp2 = new SqlParameter();
                SqlParameter sp3 = new SqlParameter();
                /*SqlParameter sp4 = new SqlParameter();
                SqlParameter sp5 = new SqlParameter();
                SqlParameter sp6 = new SqlParameter();
                SqlParameter sp7 = new SqlParameter();
                SqlParameter sp8 = new SqlParameter();*/
                con = new SqlConnection("server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook;Trusted_Connection=False");

                cmd = new SqlCommand("dbo.updaterecette", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                cmd.Parameters.Add("@idrec", SqlDbType.VarChar, 50).Value = Request.QueryString["id_recette"];
                
                cmd.Parameters.Add("@nom", SqlDbType.VarChar, 50).Value = TextBox1.Text;
                cmd.Parameters.Add("@soust", SqlDbType.VarChar,50).Value = TextBox2.Text;
                cmd.Parameters.Add("@nbp", SqlDbType.VarChar, 50).Value = TextBox3.Text;
                cmd.Parameters.Add("@tpsprep", SqlDbType.Int).Value = Int32.Parse(TextBox4.Text);
                cmd.Parameters.Add("@tpscuis", SqlDbType.Int).Value = Int32.Parse(TextBox5.Text);
                cmd.Parameters.Add("@cout", SqlDbType.Int).Value = Int32.Parse(TextBox6.Text);
                cmd.Parameters.Add("@difficulte", SqlDbType.Int).Value = Int32.Parse(TextBox7.Text);
                cmd.Parameters.Add("@source", SqlDbType.VarChar, 50).Value = TextBox8.Text;
                cmd.Parameters.Add("@prep", SqlDbType.Text).Value = TextBox9.Text;
                cmd.Parameters.Add("@real", SqlDbType.Text).Value = TextBox10.Text;
                //cmd.Parameters.Add("@Unite", SqlDbType.VarChar).Value = ddlIngredients.Text;

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Response.Write(ex.Message);
                }
                con.Close();
            }
            if (ajout)
            {
                if (quantite.Text != string.Empty)
                {

                    DataSet ds = new DataSet();
                    SqlConnection con;
                    //Here we declare the parameter which we have to use in our application
                    SqlCommand cmd = new SqlCommand();
                    SqlParameter sp1 = new SqlParameter();
                    SqlParameter sp2 = new SqlParameter();
                    SqlParameter sp3 = new SqlParameter();

                    con = new SqlConnection("server=FORMA1614;Database=magicrecettes;User ID=magicrecettes;Password=cook;Trusted_Connection=False");

                    cmd = new SqlCommand("dbo.createcomposition", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    cmd.Parameters.Add("@Recette_id", SqlDbType.VarChar, 50).Value = Request.QueryString["id_recette"];

                    cmd.Parameters.Add("@Ingredient_id", SqlDbType.VarChar, 50).Value = DropDownListIng.SelectedItem.Value;
                    cmd.Parameters.Add("@Quantite", SqlDbType.Int).Value = Int32.Parse(quantite.Text);

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        Response.Write(ex.Message);
                    }
                    con.Close();
                }
                else
                {
                    Response.Write("compléter la quantité");
                }

            }

        }

        protected void TextBox1_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox2_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox3_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox4_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox5_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox6_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox7_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox9_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox10_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void TextBox8_TextChanged(object sender, EventArgs e)
        {
            Response.Write("texte modifié");
            achange = true;
        }

        protected void DropDownListIng_SelectedIndexChanged(object sender, EventArgs e)
        {
            ajout = true;
        }

        protected void TextBox11_TextChanged(object sender, EventArgs e)
        {

        }
    }
}